package cn.com.dhcc.turbo.dao.stat;

import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.dhcc.app.core.CM;
import cn.com.dhcc.app.core.dao.BaseDao;
import cn.com.dhcc.app.core.exception.DaoException;
import cn.com.dhcc.app.core.service.Log;
import cn.com.dhcc.app.core.service.Log.LOGTYPE;
import cn.com.dhcc.turbo.entity.stat.FreqStat;
import cn.com.dhcc.turbo.entity.stat.wapper.FreqStatRowWapper;

@Repository
public class FreqStatDao extends BaseDao {
	@SuppressWarnings("deprecation")
	private static Logger logger = Log.getExistLogger(LOGTYPE.TURBO);
	
	/**
	 * 按月按节点统计
	 * @param condition
	 * @param id
	 * @param time
	 * @return
	 * @throws DaoException
	 */
	public List<FreqStat> getNodeFreqStat(String id, String time, String timeType) throws DaoException {
		String msg = "";
		String tableType = "";
		String timeModel = "";
		if(timeType.equals("month")){
			tableType = "day";
			timeModel = "yyyy-mm";
		}else{
			tableType = "month";
			timeModel = "yyyy";
		}
		String sql = "select send.st_value send_st_value,"
				+            "recv.st_value recv_st_value,"
				+            "send.recv_node_name recv_node_name,"
				+            "(send.st_value + recv.st_value) orderVal "
				+      " from ( select sum (st_value) st_value,"
				+                     "t5.node_name recv_node_name,"
				+                     "t1.recv_sysid "
				+                "from " + SI_TABLE("turbo_sys_st_sr_" + tableType) + " t1 "
				+                "left join " + SI_TABLE("turbo_sys_info") + " t2 "
				+                       "on t1.send_sysid = t2.id "
				+                "left join " + SI_TABLE("turbo_node_info") + " t3 "
				+                       "on t2.node_id = t3.id "
				+                "left join " + SI_TABLE("turbo_sys_info") + " t4 "
				+                       "on t1.recv_sysid = t4.id "
				+                "left join " + SI_TABLE("turbo_node_info") + " t5 "
				+                       "on t4.node_id = t5.id "
				+               "where t3.islocal = 1 and t5.islocal = 0 "
				+                 "and t2.node_id = ? "
				+                 "and to_char(t1.st_time, '" + timeModel +"' ) = ? "
				+               "group by t5.node_name, t1.recv_sysid) send "
				+       "join ( select sum (st_value) st_value, t3.node_name, t1.send_sysid "
				+                "from " + SI_TABLE("turbo_sys_st_sr_" + tableType) + " t1 "
				+                "left join " + SI_TABLE("turbo_sys_info") + " t2 "
				+                  "on t1.send_sysid = t2.id "
				+                "left join " + SI_TABLE("turbo_node_info") + " t3 "
				+                  "on t2.node_id = t3.id "
				+                "left join " + SI_TABLE("turbo_sys_info") + " t4 "
				+                  "on t1.recv_sysid = t4.id "
				+                "left join " + SI_TABLE("turbo_node_info") + " t5 "
				+                  "on t4.node_id = t5.id "
				+               "where t3.islocal = 0 and t5.islocal = 1 "
				+                 "and t4.node_id = ? "
				+                 "and to_char(t1.st_time, '" + timeModel +"' ) = ? "
				+               "group by t3.node_name, t1.send_sysid) recv "
				+       "on send.recv_sysid = recv.send_sysid "
				+ "order by orderVal desc";
		try {
			return CM.getDao().list("获取数据统计", sql, new FreqStatRowWapper(),
					 new Object[] {id,time,id,time});//id,time,id,time
		} catch (DaoException e) {
			msg = "获取数据统计失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 按系统统计
	 * @param condition
	 * @param id
	 * @param time
	 * @return
	 * @throws DaoException
	 */
	public List<FreqStat> getSysFreqStat(String id, String time, String timeType) throws DaoException {
		String msg = "";
		String tableType = "";
		String timeModel = "";
		if(timeType.equals("month")){
			tableType = "day";
			timeModel = "yyyy-mm";
		}else{
			tableType = "month";
			timeModel = "yyyy";
		}
		String sql = "select send.st_value send_st_value,"
				+            "recv.st_value recv_st_value,"
				+            "send.recv_node_name recv_node_name,"
				+            "send.recv_sys_name recv_sys_name,"
				+            "(send.st_value + recv.st_value) orderVal"
				+      " from ( select sum (st_value) st_value,"
				+                     "t4.sys_name recv_sys_name,"
				+                     "t5.node_name recv_node_name,"
				+                     "t1.recv_sysid "
				+                "from " + SI_TABLE("turbo_sys_st_sr_" + tableType) + " t1 "
				+                "left join " + SI_TABLE("turbo_sys_info") + " t2 "
				+                       "on t1.send_sysid = t2.id "
				+                "left join " + SI_TABLE("turbo_node_info") + " t3 "
				+                       "on t2.node_id = t3.id "
				+                "left join " + SI_TABLE("turbo_sys_info") + " t4 "
				+                       "on t1.recv_sysid = t4.id "
				+                "left join " + SI_TABLE("turbo_node_info") + " t5 "
				+                       "on t4.node_id = t5.id "
				+               "where t3.islocal = 1 and t5.islocal = 0 "
				+                 "and t1.send_sysid = ? "
				+                 "and to_char(t1.st_time, '" + timeModel +"' ) = ? "
				+               "group by t4.sys_name, t2.sys_name, t5.node_name, t1.recv_sysid) send "
				+       "join ( select sum (st_value) st_value, t2.sys_name, t3.node_name, t1.send_sysid "
				+                "from " + SI_TABLE("turbo_sys_st_sr_" + tableType) + " t1 "
				+                "left join " + SI_TABLE("turbo_sys_info") + " t2 "
				+                  "on t1.send_sysid = t2.id "
				+                "left join " + SI_TABLE("turbo_node_info") + " t3 "
				+                  "on t2.node_id = t3.id "
				+                "left join " + SI_TABLE("turbo_sys_info") + " t4 "
				+                  "on t1.recv_sysid = t4.id "
				+                "left join " + SI_TABLE("turbo_node_info") + " t5 "
				+                  "on t4.node_id = t5.id "
				+               "where t3.islocal = 0 and t5.islocal = 1 "
				+                 "and t1.recv_sysid = ? "
				+                 "and to_char(t1.st_time, '" + timeModel +"' ) = ? "
				+               "group by t2.sys_name, t3.node_name, t1.send_sysid) recv "
				+       "on send.recv_sysid = recv.send_sysid "
				+ "order by orderVal desc ";
		try {
			return CM.getDao().list("获取数据统计", sql, new FreqStatRowWapper(),
					new Object[] {id,time,id,time});//id,time,id,time 
		} catch (DaoException e) {
			msg = "获取数据统计失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
}
